package com.yikome.delivery.service;

import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.google.common.base.Preconditions;
import com.google.common.collect.ImmutableList;
import com.google.common.collect.ImmutableMap;
import com.itmuch.lightsecurity.jwt.User;
import com.yikome.common.CommonService;
import com.yikome.delivery.dto.DeliveryDetailsSearchDTO;
import com.yikome.delivery.dto.DeliveryExportDTO;
import com.yikome.delivery.dto.DeliverySearchDTO;
import com.yikome.delivery.dto.GenerateDeliveryOrderSearchDTO;
import com.yikome.delivery.dto.GenerateDeliveryOrderSearchOrderDTO;
import com.yikome.delivery.dto.GenerateDeliveryOrderSearchOrderProductDTO;
import com.yikome.delivery.dto.GenerateDeliveryProductValidateSearchDTO;
import com.yikome.delivery.dto.OrderDeliverySearchDTO;
import com.yikome.delivery.entity.Delivery;
import com.yikome.delivery.entity.DeliveryDetails;
import com.yikome.delivery.enums.DeliveryStatus;
import com.yikome.delivery.repository.DeliveryDetailsRepository;
import com.yikome.delivery.repository.DeliveryRepository;
import com.yikome.delivery.request.DeliverySearchRequest;
import com.yikome.delivery.request.DeliverySendOutRequest;
import com.yikome.delivery.schedule.AsyncDeliverySchedule;
import com.yikome.order.entity.Order;
import com.yikome.order.enums.OrderStatus;
import com.yikome.order.service.OrderService;
import com.yikome.storehouse.entity.Inventory;
import com.yikome.storehouse.entity.Storehouse;
import com.yikome.storehouse.repository.InventoryRepository;
import com.yikome.storehouse.repository.StorehouseRepository;
import com.yikome.tracker.htc.HCTLogisticsResponse;
import com.yikome.tracker.htc.HCTLogisticsUtils;
import com.yikome.utils.BeanUtils;
import com.yikome.utils.NamedParameterJdbcUtils;
import com.yikome.utils.SecurityUtils;

import cn.hutool.core.util.StrUtil;

@Service
public class DeliveryService extends CommonService<Delivery, Long> {

	@Autowired
	private DeliveryRepository deliveryRepository; 
	
	@Autowired
	private DeliveryDetailsRepository deliveryDetailsRepository;
	
	@Autowired
	private InventoryRepository inventoryRepository;
	
	@Autowired
	private OrderService orderService;
	
	@Autowired
	private StorehouseRepository storehouseRepository;
	
	@Autowired
	private AsyncDeliverySchedule deliverySchedule ; 
	
	@Autowired
	private NamedParameterJdbcTemplate jdbcTemplate; 
	
	/**
	 * 查询logisticsCode
	 * 查询物流轨迹
	 */
	public List<HCTLogisticsResponse> findOrderDeliveryTrack(String orderCode) {
		// TODO Auto-generated method stub
		
		Map<String, Object> paramMap = ImmutableMap.of("orderCode", orderCode + "%");
		
		String countsSQL = "SELECT COUNT(DISTINCT d_delivery.logistics_code) FROM d_delivery WHERE d_delivery.yn = 1 AND d_delivery.order_code LIKE :orderCode " ;
		
		Long counts = jdbcTemplate.queryForObject(countsSQL, paramMap, Long.class);
		
		if(counts == 0) return ImmutableList.of();
		
		String contentSQL = "SELECT DISTINCT d_delivery.logistics_code FROM d_delivery WHERE d_delivery.yn = 1 AND d_delivery.order_code LIKE :orderCode " ;
		
		List<String> logisticsCodes = jdbcTemplate.queryForList(contentSQL, paramMap, String.class);
		
		if(logisticsCodes == null || logisticsCodes.isEmpty()) return ImmutableList.of();
		
		return HCTLogisticsUtils.getHCTLogisticsDTO(logisticsCodes);
	}
	
	@Transactional
	public void generateDelivery() {
		
		String orderSearchSQL = "SELECT \n" + 
								"	o_order.storehouse_id, o_order.`code` order_code, IF(o_order.payment = 1, o_order.price, 0) delivery_price, o_order.currency, o_order.remark, o_order.consignee, o_order.tel, o_order.contry, o_order.province, o_order.city, o_order.district, o_order.details, o_order.zip_code, o_order.email, \n" + 
								"	o_order_product.product_id, o_order_product.product_title, o_order_product.product_sku_barcode, o_order_product.product_sku_name, o_order_product.product_price, o_order_product.product_image_url, o_order_product.quantity \n" + 
								"FROM o_order \n" + 
								"	LEFT JOIN o_order_product ON (o_order.`code` = o_order_product.order_code) \n" + 
								"WHERE 1 = 1 \n" + 
								"AND o_order.yn = 1 AND o_order_product.yn = 1 \n" + 
								"AND o_order.`status` = :orderStatus \n" + 
								"ORDER BY o_order.priority DESC, o_order.created_at ASC \n" ; 
		
		List<GenerateDeliveryOrderSearchDTO> generateDeliveryOrderSearchDTOs = jdbcTemplate.query(orderSearchSQL, ImmutableMap.of("orderStatus", OrderStatus.ORDER_STOCK.getStatus()), new BeanPropertyRowMapper<>(GenerateDeliveryOrderSearchDTO.class));
		
		Map<String, GenerateDeliveryOrderSearchOrderDTO> generateDeliveryOrderSearchMap = new LinkedHashMap<>();
		
		for (GenerateDeliveryOrderSearchDTO generateDeliveryOrderSearchDTO : generateDeliveryOrderSearchDTOs) {
			if(generateDeliveryOrderSearchMap.containsKey(generateDeliveryOrderSearchDTO.getOrderCode())) {
			
				GenerateDeliveryOrderSearchOrderProductDTO orderSearchOrderProductDTO = new GenerateDeliveryOrderSearchOrderProductDTO();
				BeanUtils.copyProperties(generateDeliveryOrderSearchDTO, orderSearchOrderProductDTO);
				generateDeliveryOrderSearchMap.get(generateDeliveryOrderSearchDTO.getOrderCode()).getProducts().add(orderSearchOrderProductDTO);
			}else {
				
				GenerateDeliveryOrderSearchOrderDTO orderSearchOrderDTO = new GenerateDeliveryOrderSearchOrderDTO();
				BeanUtils.copyProperties(generateDeliveryOrderSearchDTO, orderSearchOrderDTO);
				
				List<GenerateDeliveryOrderSearchOrderProductDTO> products = new ArrayList<>();
				GenerateDeliveryOrderSearchOrderProductDTO orderSearchOrderProductDTO = new GenerateDeliveryOrderSearchOrderProductDTO();
				BeanUtils.copyProperties(generateDeliveryOrderSearchDTO, orderSearchOrderProductDTO);
				products.add(orderSearchOrderProductDTO);
				orderSearchOrderDTO.setProducts(products);
				
				generateDeliveryOrderSearchMap.put(generateDeliveryOrderSearchDTO.getOrderCode(), orderSearchOrderDTO);
			}
		}
		
		Collection<GenerateDeliveryOrderSearchOrderDTO> deliveryOrderSearchOrderDTOs = generateDeliveryOrderSearchMap.values();
		
		if(deliveryOrderSearchOrderDTOs == null || deliveryOrderSearchOrderDTOs.isEmpty())return ;
		
		for (GenerateDeliveryOrderSearchOrderDTO deliveryOrderSearchOrderDTO : deliveryOrderSearchOrderDTOs) {
			
			checkCreateDelivery(deliveryOrderSearchOrderDTO);
		}
	}
	
	/**
	 * TODO
	 * 0. 备用仓备货问题处理入口：
	 * 1. 备用仓是否满足订单商品需求？
	 * 1.1. 是 则直接走备用仓 
	 * 1.2.否 主仓是否满足订单需求？
	 * 1.2.1.是： 则走主仓
	 * 1.2.2.否： 则跳过
	 */
	@Transactional
	public void checkCreateDelivery(GenerateDeliveryOrderSearchOrderDTO deliveryOrderSearchOrderDTO) {
		List<GenerateDeliveryOrderSearchOrderProductDTO> products = deliveryOrderSearchOrderDTO.getProducts();
		
		if(products == null || products.isEmpty()) return ;
		
		String productValidateSQL = "SELECT  \n" + 
									"	s_inventory.product_sku_barcode, s_inventory.inventory, \n" + 
									"	IFNULL((SELECT SUM(d_delivery_details.quantity) FROM d_delivery LEFT JOIN d_delivery_details ON (d_delivery.delivery_code = d_delivery_details.delivery_code) WHERE d_delivery.yn = 1 AND d_delivery_details.yn = 1 AND d_delivery.storehouse_id = s_inventory.storehouse_id AND d_delivery.`status` <= :deliveryStatus AND d_delivery_details.product_sku_barcode = s_inventory.product_sku_barcode),0) delivery_product_quantity \n" + 
									"FROM s_inventory  \n" + 
									"WHERE s_inventory.yn = 1 \n" + 
									"AND s_inventory.storehouse_id = :storehouseId \n" + 
									"AND s_inventory.product_sku_barcode IN (:productSkuBarcodes)  \n" ; 
		
		Set<String> productSkuBarcodes = products.stream().map(GenerateDeliveryOrderSearchOrderProductDTO::getProductSkuBarcode).collect(Collectors.toSet());
		
		Storehouse clearStorehouse = storehouseRepository.findClearStorehouse(deliveryOrderSearchOrderDTO.getStorehouseId());
		
		Boolean isClearSuccess = Boolean.FALSE ;
		
		if(clearStorehouse != null && StrUtil.isNotBlank(clearStorehouse.getRemoteStorehouseCode())) {
			
			List<GenerateDeliveryProductValidateSearchDTO> deliveryProductValidateSearchDTOs = jdbcTemplate.query(productValidateSQL, ImmutableMap.of("storehouseId" , clearStorehouse.getId() , "productSkuBarcodes", productSkuBarcodes, "deliveryStatus", DeliveryStatus.DELIVERY_ASYNC.getStatus()) , new BeanPropertyRowMapper<>(GenerateDeliveryProductValidateSearchDTO.class));
			
			if(deliveryProductValidateSearchDTOs.size() == productSkuBarcodes.size()) {
				
				boolean validateProductQuantity = validateProductQuantity(products, deliveryProductValidateSearchDTOs);
				if(validateProductQuantity) {
					
					deliveryOrderSearchOrderDTO.setStorehouseId(clearStorehouse.getId());
					createDelivery(deliveryOrderSearchOrderDTO);
					
					isClearSuccess = Boolean.TRUE ;
				}
				
			}
		}
		
		if(!isClearSuccess) {
			
			List<GenerateDeliveryProductValidateSearchDTO> deliveryProductValidateSearchDTOs = jdbcTemplate.query(productValidateSQL, ImmutableMap.of("storehouseId" , deliveryOrderSearchOrderDTO.getStorehouseId() , "productSkuBarcodes", productSkuBarcodes, "deliveryStatus", DeliveryStatus.DELIVERY_ASYNC.getStatus()) , new BeanPropertyRowMapper<>(GenerateDeliveryProductValidateSearchDTO.class));
			
			if(deliveryProductValidateSearchDTOs.size() != productSkuBarcodes.size()) return;
			
			boolean validateProductQuantity = validateProductQuantity(products, deliveryProductValidateSearchDTOs);
			
			if(validateProductQuantity) createDelivery(deliveryOrderSearchOrderDTO);
		}
	}

	//校验数量
	private boolean validateProductQuantity(List<GenerateDeliveryOrderSearchOrderProductDTO> products , List<GenerateDeliveryProductValidateSearchDTO> deliveryProductValidateSearchDTOs) {
		
		for (GenerateDeliveryOrderSearchOrderProductDTO generateDeliveryOrderSearchOrderDTO : products) {
			
			GenerateDeliveryProductValidateSearchDTO deliveryProductValidateSearchDTO = findGenerateDeliveryProductValidateSearchDTO(generateDeliveryOrderSearchOrderDTO , deliveryProductValidateSearchDTOs);
			if(deliveryProductValidateSearchDTO == null) return false;
			
			int num = deliveryProductValidateSearchDTO.getInventory() - deliveryProductValidateSearchDTO.getDeliveryProductQuantity() - generateDeliveryOrderSearchOrderDTO.getQuantity();
			if(num < 0) return false ; 
			
		}
		
		return true ; 
	}

	private GenerateDeliveryProductValidateSearchDTO findGenerateDeliveryProductValidateSearchDTO(GenerateDeliveryOrderSearchOrderProductDTO generateDeliveryOrderSearchOrderDTO, List<GenerateDeliveryProductValidateSearchDTO> deliveryProductValidateSearchDTOs) {

		for (GenerateDeliveryProductValidateSearchDTO generateDeliveryProductValidateSearchDTO : deliveryProductValidateSearchDTOs) {
			if(StrUtil.equalsIgnoreCase(generateDeliveryProductValidateSearchDTO.getProductSkuBarcode(), generateDeliveryOrderSearchOrderDTO.getProductSkuBarcode())) return generateDeliveryProductValidateSearchDTO ; 
		}
		
		return null;
	}
	
	@Transactional
	public Delivery createDelivery(GenerateDeliveryOrderSearchOrderDTO deliveryOrderSearchOrderDTO) {
		
		if(deliveryOrderSearchOrderDTO.getProducts().isEmpty()) return null;
		
		Delivery delivery = new Delivery();
		delivery.setStorehouseId(deliveryOrderSearchOrderDTO.getStorehouseId());
		delivery.setDeliveryPrice(deliveryOrderSearchOrderDTO.getDeliveryPrice());
		delivery.setStatus(DeliveryStatus.DELIVERY_CREATED.getStatus());
		delivery.setRemark(deliveryOrderSearchOrderDTO.getRemark());
		delivery.setConsignee(deliveryOrderSearchOrderDTO.getConsignee());
		delivery.setTel(deliveryOrderSearchOrderDTO.getTel());
		delivery.setContry(deliveryOrderSearchOrderDTO.getContry());
		delivery.setProvince(deliveryOrderSearchOrderDTO.getProvince());
		delivery.setCity(deliveryOrderSearchOrderDTO.getCity());
		delivery.setDistrict(deliveryOrderSearchOrderDTO.getDistrict());
		delivery.setZipCode(deliveryOrderSearchOrderDTO.getZipCode());
		delivery.setDetail(deliveryOrderSearchOrderDTO.getDetails());
		delivery.setEmail(deliveryOrderSearchOrderDTO.getEmail());
		delivery.setOrderCode(deliveryOrderSearchOrderDTO.getOrderCode());
		delivery.setCurrency(deliveryOrderSearchOrderDTO.getCurrency());
		
		deliveryRepository.save(delivery);
		delivery.setDeliveryCode("HMB" + StrUtil.padPre(String.valueOf(delivery.getId()), 10, '0'));
		deliveryRepository.save(delivery);
		
		List<DeliveryDetails> deliveryDetails = new ArrayList<>();
		List<GenerateDeliveryOrderSearchOrderProductDTO> products = deliveryOrderSearchOrderDTO.getProducts();
		for (GenerateDeliveryOrderSearchOrderProductDTO generateDeliveryOrderSearchOrderProductDTO : products) {
			DeliveryDetails details = new DeliveryDetails();
			details.setDeliveryCode(delivery.getDeliveryCode());
			details.setProductId(generateDeliveryOrderSearchOrderProductDTO.getProductId());
			details.setProductTitle(generateDeliveryOrderSearchOrderProductDTO.getProductTitle());
			details.setProductSkuBarcode(generateDeliveryOrderSearchOrderProductDTO.getProductSkuBarcode());
			details.setProductSkuName(generateDeliveryOrderSearchOrderProductDTO.getProductSkuName());
			details.setProductPrice(generateDeliveryOrderSearchOrderProductDTO.getProductPrice());
			details.setProductImageUrl(generateDeliveryOrderSearchOrderProductDTO.getProductImageUrl());
			details.setQuantity(generateDeliveryOrderSearchOrderProductDTO.getQuantity());
			deliveryDetails.add(details);
		}
		deliveryDetailsRepository.saveAll(deliveryDetails);
		
		deliverySchedule.asyncDeliveryCreate(delivery, deliveryDetails);
		
		Order order = orderService.findOrder(delivery.getOrderCode());
		order.setDeliveryCode(delivery.getDeliveryCode());
		order.setStatus(OrderStatus.ORDER_DISTRIBUTION.getStatus());
		order.setStorehouseId(delivery.getStorehouseId());
		orderService.changeOrderStatus(order);
		
		return delivery ; 
	}

	public Page<DeliverySearchDTO> findDeliverySearchDTO(DeliverySearchRequest deliverySearchRequest, Pageable pageable) {

		User currentUser = SecurityUtils.getCurrentUser();
		
		String contenSQL =  "SELECT \n" + 
							"	d_delivery.id delivery_id, s_storehouse.storehouse_name, d_delivery.delivery_code, d_delivery.`status`, d_delivery.remark, d_delivery.created_at, \n" + 
							"	d_delivery.consignee, d_delivery.tel, d_delivery.city, d_delivery.district, d_delivery.detail, d_delivery.delivery_price,  \n" + 
							"	d_delivery.logistics_code, d_delivery.payment_amount, d_delivery.shipping_freight \n" + 
							"FROM d_delivery \n" + 
							"	LEFT JOIN s_storehouse ON (d_delivery.storehouse_id = s_storehouse.id) \n" ; 
		
		StringBuilder paramsSQL = new StringBuilder("WHERE d_delivery.yn = 1 \n");
		Map<String , Object> paramsMap = new HashMap<>();
		
		if(deliverySearchRequest.getStorehouseId() != null) {
			paramsSQL.append("AND d_delivery.storehouse_id = :storehouseId \n");
			paramsMap.put("storehouseId", deliverySearchRequest.getStorehouseId());
		}
		if(deliverySearchRequest.getStatus() != null) {
			paramsSQL.append("AND d_delivery.`status` = :status \n");
			paramsMap.put("status", deliverySearchRequest.getStatus());
		}
        if(StrUtil.isNotBlank(deliverySearchRequest.getDeliveryCode())) {
            List<String> deliveryCodes = StrUtil.split(deliverySearchRequest.getDeliveryCode().replaceAll("\\s", ","), ',');
            if(deliveryCodes.size() == 1) {
                paramsSQL.append("AND d_delivery.delivery_code = :deliveryCode \n");
                paramsMap.put("deliveryCode", deliverySearchRequest.getDeliveryCode());
            }else {
                paramsSQL.append("AND d_delivery.delivery_code in (:deliveryCodes) \n");
                paramsMap.put("deliveryCodes", deliveryCodes);
            }
        }
        if(StrUtil.isNotBlank(deliverySearchRequest.getTel())) {
            List<String> tels = StrUtil.split(deliverySearchRequest.getTel().replaceAll("\\s", ","), ',');
            if(tels.size() == 1) {
                paramsSQL.append("AND d_delivery.tel = :tel \n");
                paramsMap.put("tel", deliverySearchRequest.getTel());
            }else {
                paramsSQL.append("AND d_delivery.tel in (:tels) \n");
                paramsMap.put("tels", tels);
            }
        }
        if(StrUtil.isNotBlank(deliverySearchRequest.getLogisticsCode())) {
            List<String> logisticsCodes = StrUtil.split(deliverySearchRequest.getLogisticsCode().replaceAll("\\s", ","), ',');
            if(logisticsCodes.size() == 1) {
                paramsSQL.append("AND d_delivery.logistics_code = :logisticsCode \n");
                paramsMap.put("logisticsCode", deliverySearchRequest.getLogisticsCode());
            }else {
                paramsSQL.append("AND d_delivery.logistics_code in (:logisticsCodes) \n");
                paramsMap.put("logisticsCodes", logisticsCodes);
            }
        }
		List<String> roles = currentUser.getRoles();
		if(roles.size() == 1 && roles.contains("storehouse")) {
			paramsSQL.append("AND s_storehouse.managers like :userName \n");
			paramsMap.put("userName", StrUtil.wrap(currentUser.getUsername(), "%"));
		}
		
		if(StrUtil.isNotBlank(deliverySearchRequest.getProductId())) {
			paramsSQL.append("AND d_delivery.delivery_code IN (select d_delivery_details.delivery_code from d_delivery_details where d_delivery_details.yn = 1 and d_delivery_details.product_id in (:productId)) \n");
			List<String> productIds = StrUtil.split(deliverySearchRequest.getProductId().replaceAll("\\s", ","), ',');
			paramsMap.put("productId", productIds);
		}
		
		
		if(deliverySearchRequest.getBegainTime() != null && deliverySearchRequest.getEndTime() != null) {
			paramsSQL.append("AND d_delivery.created_at BETWEEN FROM_UNIXTIME(:begainTime) AND FROM_UNIXTIME(:endTime) \n");
			paramsMap.put("begainTime", deliverySearchRequest.getBegainTime());
			paramsMap.put("endTime", deliverySearchRequest.getEndTime());
		}
		
		String orderSQL = NamedParameterJdbcUtils.findOrderSQL(pageable);
		String pageSQL = NamedParameterJdbcUtils.findPageSQL(pageable);
		
		List<DeliverySearchDTO> orderSearchDTOs = jdbcTemplate.query(contenSQL + paramsSQL + orderSQL + pageSQL, paramsMap, new BeanPropertyRowMapper<>(DeliverySearchDTO.class));
		
		for (DeliverySearchDTO deliverySearchDTO : orderSearchDTOs) {
			deliverySearchDTO.setStatusName(DeliveryStatus.name(deliverySearchDTO.getStatus()).getStatusName());
		}
		
		Long total = jdbcTemplate.queryForObject("SELECT COUNT(1) FROM (" + contenSQL + paramsSQL + ") T", paramsMap, Long.class);
		
		return new PageImpl<>(orderSearchDTOs, pageable, total);
	}
	
	public List<OrderDeliverySearchDTO> findOrderDeliveries(String orderCode) {
		
		String contentSQL = "SELECT d_delivery.order_code, d_delivery.delivery_code, d_delivery.`status`, d_delivery.logistics_code, d_delivery.logistics_company FROM d_delivery WHERE d_delivery.yn = 1 AND d_delivery.order_code = :orderCode " ; 
		
		List<OrderDeliverySearchDTO> deliverySearchDTOs  = jdbcTemplate.query(contentSQL, ImmutableMap.of("orderCode", orderCode), new BeanPropertyRowMapper<>(OrderDeliverySearchDTO.class));
	
		for (OrderDeliverySearchDTO orderDeliverySearchDTO : deliverySearchDTOs) {
			orderDeliverySearchDTO.setDeliveryStatusName(DeliveryStatus.name(orderDeliverySearchDTO.getStatus()).getStatusName());
		}
		
		return deliverySearchDTOs ; 
	}

	public List<DeliveryDetailsSearchDTO> findDeliveryDetails(String deliveryCode) {
		
		List<DeliveryDetailsSearchDTO> results = new ArrayList<>();
		
		List<DeliveryDetails> deliveryDetails = deliveryDetailsRepository.findDeliveryDetails(deliveryCode , Sort.by(Direction.DESC, "productId" , "productSkuBarcode"));
		
		for (DeliveryDetails deliveryDetail : deliveryDetails) {
			DeliveryDetailsSearchDTO detailsSearchDTO = new DeliveryDetailsSearchDTO();
			BeanUtils.copyProperties(deliveryDetail, detailsSearchDTO);
			detailsSearchDTO.setDeliveryDetailsId(deliveryDetail.getId());
			
			results.add(detailsSearchDTO);
		}
		return results;
	}
	
	public List<DeliveryExportDTO> findDeliveryExportDTO(DeliverySearchRequest deliverySearchRequest, Pageable pageable) {

		String contenSQL =  "SELECT \n" + 
							"	d_delivery.id delivery_id, s_storehouse.storehouse_name, d_delivery.delivery_code, d_delivery.`status`, d_delivery.remark, d_delivery.created_at, \n" + 
							"	d_delivery.consignee, d_delivery.tel, d_delivery.city, d_delivery.district, d_delivery.detail, d_delivery.delivery_price, \n" + 
							"	d_delivery.logistics_code, d_delivery.payment_amount, d_delivery.shipping_freight, \n" +
							"  d_delivery_details.product_id, d_delivery_details.product_title, d_delivery_details.product_sku_barcode, d_delivery_details.product_sku_name, d_delivery_details.product_price, d_delivery_details.product_image_url, d_delivery_details.quantity \n" +
							"FROM d_delivery \n" + 
							"	LEFT JOIN s_storehouse ON (d_delivery.storehouse_id = s_storehouse.id) \n" +
							"	LEFT JOIN d_delivery_details ON (d_delivery.delivery_code = d_delivery_details.delivery_code AND d_delivery_details.yn = 1) \n" ;
		
		StringBuilder paramsSQL = new StringBuilder("WHERE d_delivery.yn = 1 \n");
		Map<String , Object> paramsMap = new HashMap<>();
		
		if(deliverySearchRequest.getStorehouseId() != null) {
			paramsSQL.append("AND d_delivery.storehouse_id = :storehouseId \n");
			paramsMap.put("storehouseId", deliverySearchRequest.getStorehouseId());
		}
		if(StrUtil.isNotBlank(deliverySearchRequest.getDeliveryCode())) {
			paramsSQL.append("AND d_delivery.delivery_code = :deliveryCode \n");
			paramsMap.put("deliveryCode", deliverySearchRequest.getDeliveryCode().trim());
		}
		if(deliverySearchRequest.getStatus() != null) {
			paramsSQL.append("AND d_delivery.`status` = :status \n");
			paramsMap.put("status", deliverySearchRequest.getStatus());
		}
		if(StrUtil.isNotBlank(deliverySearchRequest.getTel())) {
			paramsSQL.append("AND d_delivery.tel = :tel \n");
			paramsMap.put("tel", deliverySearchRequest.getTel().trim());
		}
		if(StrUtil.isNotBlank(deliverySearchRequest.getLogisticsCode())) {
			paramsSQL.append("AND d_delivery.logistics_code = :logisticsCode \n");
			paramsMap.put("logisticsCode", deliverySearchRequest.getLogisticsCode().trim());
		}
		if(deliverySearchRequest.getBegainTime() != null && deliverySearchRequest.getEndTime() != null) {
			paramsSQL.append("AND d_delivery.created_at BETWEEN FROM_UNIXTIME(:begainTime) AND FROM_UNIXTIME(:endTime) \n");
			paramsMap.put("begainTime", deliverySearchRequest.getBegainTime());
			paramsMap.put("endTime", deliverySearchRequest.getEndTime());
		}
		
		String orderSQL = NamedParameterJdbcUtils.findOrderSQL(pageable);
		
		List<DeliveryExportDTO> deliveryExportDTOs = jdbcTemplate.query(contenSQL + paramsSQL + orderSQL, paramsMap, new BeanPropertyRowMapper<>(DeliveryExportDTO.class));
		
		for (DeliveryExportDTO deliveryExportDTO : deliveryExportDTOs) {
			deliveryExportDTO.setStatusName(DeliveryStatus.name(deliveryExportDTO.getStatus()).getStatusName());
		}
		
		return deliveryExportDTOs;
	}

	@Transactional
	public Delivery deliverySendOut(DeliverySendOutRequest deliverySendOutRequest) {
		Delivery delivery = deliveryRepository.findDelivery(deliverySendOutRequest.getDeliveryCode());
		Preconditions.checkArgument(delivery != null, "发货单不存在...");
		Preconditions.checkArgument(delivery.getStatus() >= DeliveryStatus.DELIVERY_CREATED.getStatus() && delivery.getStatus() < DeliveryStatus.DELIVERY_SEND.getStatus(), "状态不允许操作");
		
		List<DeliveryDetails> deliveryDetails = deliveryDetailsRepository.findDeliveryDetails(delivery.getDeliveryCode());
		Preconditions.checkArgument(deliveryDetails != null && !deliveryDetails.isEmpty(), "发货单明细不存在...");
		
		for (DeliveryDetails sku : deliveryDetails) {
			Inventory inventory = inventoryRepository.findInventory(delivery.getStorehouseId(), sku.getProductSkuBarcode());
			Preconditions.checkArgument(inventory != null, "{}库存不存在..." , sku.getProductSkuBarcode());
			
			inventory.setInventory(Math.abs(inventory.getInventory()) - Math.abs(sku.getQuantity()));
			Preconditions.checkArgument(inventory.getInventory() >= 0, "{}库存不能为负..." , sku.getProductSkuBarcode());
			
			inventoryRepository.save(inventory);
		}
		
		delivery.setLogisticsCode(deliverySendOutRequest.getLogisticsCode());
		delivery.setLogisticsCompany(deliverySendOutRequest.getLogisticsCompany());
		delivery.setStatus(DeliveryStatus.DELIVERY_SEND.getStatus());
		deliveryRepository.save(delivery);
		
		Order order = orderService.findOrder(delivery.getOrderCode());
		order.setStatus(OrderStatus.ORDER_DELIVERY.getStatus());
		orderService.changeOrderStatus(order);
		
		return delivery ; 
	}

	@Transactional
	public void deleteDelivery(String deliveryCode) {
		
		Delivery delivery = deliveryRepository.findDelivery(deliveryCode);
		Preconditions.checkArgument(delivery != null, "发货单不存在...");
		Preconditions.checkArgument(delivery.getStatus() >= DeliveryStatus.DELIVERY_CREATED.getStatus() && delivery.getStatus() < DeliveryStatus.DELIVERY_SEND.getStatus(), "状态不允许操作");
		
		delivery.setYn(0);
		deliveryRepository.save(delivery);
		
		
		Order order = orderService.findOrder(delivery.getOrderCode());
		order.setDeliveryCode(null);
		order.setStatus(OrderStatus.ORDER_STOCK.getStatus());
		orderService.changeOrderStatus(order);
		
		if(delivery.getStatus() > DeliveryStatus.DELIVERY_CREATED.getStatus()) {
			deliverySchedule.asyncDeliveryDelete(deliveryCode);
		}
	}
}
